package com.mes.service.manu;

import com.mes.datasource.util.CheckSQLParamsUtil;
import com.mes.entity.DataCollectionPage;
import com.mes.entity.DataCollectionTab;
import com.mes.entity.EmesoJobOrder;
import com.mes.entity.EmesoJobOrderExe;
import com.yy.base.common.service.BaseService;
import org.springframework.stereotype.Service;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Service
public class DataCollectionService extends BaseService {
	/**
	 * 获取生产工单信息
	 * @param code
	 * @return
	 */
	public Map<String, Object> searchOrderInfo(String code, DataCollectionPage page, String userId, String orgId) {
		Map<String, Object> params = new HashMap();
		params.put("code", code);
		params.put("processId", page.getEmesProcessOprId());
		params.put("userId", userId);
		params.put("orgId", orgId);

		CheckSQLParamsUtil util = new CheckSQLParamsUtil(page.getSourceSql(), params, "1=1");
		return dao.queryUnique(util.getSql(), util.getParamValues());
	}

	/**
	 * 判断工序是否已经记录
	 * @param orderCode
	 * @return
	 */
	public EmesoJobOrder getJobOrder(String orderCode) {
		String sql = "select * from emeso_job_Order where code = ?";
		return dao.queryUnique(EmesoJobOrder.class, sql, orderCode);
	}

	/**
	 * 判断工序是否已经记录
	 * @param orderId
	 * @param processId
	 * @return
	 */
	public EmesoJobOrderExe getJobOrderExe(String orderId, String processId) {
		String sql = "select emeso_job_Order_Id id, status, Emes_Process_Id " +
				"  from emeso_job_Order_Exe " +
				" where emeso_job_Order_id = ?" +
				"	and Emes_Process_Id = ?" +
				" order by created desc";
		return dao.queryUnique(EmesoJobOrderExe.class, sql, orderId, processId);
	}

	/**
	 * 获取生产设备信息
	 *
	 * @param code
	 * @return
	 */
	public Map<String, Object> getFacilitiesInfo(String code) {
		String sql = "select Eam_Facilities_Id, name, FACILITY_TYPE , code, ORI_NUMBER "
				+ " from Eam_Facilities f where f.code = ?";
		return dao.queryUnique(sql, code);
	}

	/**
	 * 获取录入页面工序信息
	 * @param code
	 * @return
	 */
	public DataCollectionPage getDataCollectionPage(String code) {
		String sql = "select ip.* from Emes_DC_Input_Page ip where code = ?";
		return dao.queryUnique(DataCollectionPage.class, sql, code);
	}

	/**
	 * 验证工单是否完工
	 * @param code
	 * @return
	 */
	public void getfishCollectionPage(String code, String pageCode, String orgId) {
		Map<String, Object> mapEre = getDataCollectionPage(pageCode, code, orgId);
		if (mapEre != null || !mapEre.isEmpty()) {
			String sql = "select * from Emeso_Job_Order_Exe oe where exists(" +
					" select 1 from Emeso_Job_Order jo where jo.Emeso_Job_Order_id = oe.Emeso_Job_Order_id and jo.code = ? ) " +
					"and emes_process_id = ? ";
			Map<String, Object> map = dao.queryUnique(sql, code, mapEre.get("EMES_PROCESS_ID").toString());
			if (map != null || !map.isEmpty()) {
				if (map.get("STATUS").equals("FINISHED")) {
					throw new RuntimeException("工单[" + code + "]此工序已完工！");
				}
			}
		}
	}

	/**
	 * 获取录入页面工序信息
	 * @param code
	 * @return
	 */
	public Map<String, Object> getDataCollectionPage(String code, String orderCode, String orgId) {
		String sql = "select oe.* from emeso_job_order_exe oe, emeso_job_order jo\n" +
				" where exists (\n" +
				"     select 1 from emes_process p where p.emes_process_id = oe.emes_process_id \n" +
				"      and p.emes_processopr_id = (select emes_processopr_id from Emes_DC_Input_Page where code = ?)\n" +
				"  )\n" +
				"   and oe.emeso_job_order_id = jo.emeso_job_order_id\n" +
				"  and jo.code = ?";
		return dao.queryUnique(sql, code, orderCode);
	}

	/**
	 * 获取录入页面工序信息
	 * @param page
	 * @param id
	 * @return
	 */
	public List<Map<String, Object>> getHistoryList(DataCollectionPage page, String id, String userId, String orgId) {
		Map<String, Object> params = new HashMap();
		params.put("code", page.getCode());
		params.put("processId", page.getEmesProcessOprId());
		params.put("userId", userId);
		params.put("orgId", orgId);

		String sql = "select sql from Emes_DC_Input_Data d where Emes_DC_Input_Data_Id = ?";
		Map<String, Object> data = dao.queryUnique(sql, id);
		if (data != null) {
			CheckSQLParamsUtil util = new CheckSQLParamsUtil((String)data.get("SQL"), params, "1=1");
			return dao.query(util.getSql(), util.getParamValues());
		}
		return null;
	}
	/**
	 * 获取采集页面页签信息
	 * @param pageId
	 * @return
	 */
	public List<DataCollectionTab> getDataCollectionTab(String pageId) {
		String sql = "select * from Emes_DC_Input_Data where Emes_DC_Input_Page_ID = ?";
		List<DataCollectionTab> list = dao.query(DataCollectionTab.class, sql, pageId);
		for (DataCollectionTab tab : list) {
			tab.setColumns(getDataCollectionColumn(tab.getId()));
		}
		return list;
	}
	/**
	 * 获取采集页面页签信息
	 * @param dataId
	 * @return
	 */
	public List<Map<String, Object>> getDataCollectionColumn(String dataId) {
		String sql = "select name, column_name, width, is_show, data_type from emes_dc_input_data_detail where Emes_DC_Input_Data_id = ? order by seq_no";
		return dao.query(sql, dataId);
	}

	/**
	 * 存储批次对应的工序信息
	 * @param orderCode
	 * @param processId
	 * @param processName
	 * @param orgId
	 * @param userId
	 * @param description
	 * @return
	 */
	public void saveJobOrder(String orderCode, String processId, String processName, String orgId, String userId,
							 String facCode, String status, String reworkReason, String description) {
		String facId = null;
		if (facCode != null && !"".equals(facCode)) {
			Map<String, Object> map = getFacilitiesByCode(facCode);
			if (map != null) {
				facId = (String) map.get("Eam_Facilities_ID");
				//验证设备是否在工序的关联类型中
				if (!checkFacilitiesInProcess(processId, facId)) {
					throw new RuntimeException("设备[" + facCode + "]不存在于" + processName + "关联的设备类型中，请检查是否扫描错误！");
				}
			} else {
				throw new RuntimeException("设备[" + facCode + "]不存在！");
			}
		}
	}

	/**
	 * 存储订单工序执行
	 * @param orderCode
	 * @param processId
	 * @param processName
	 * @param orgId
	 * @param userId
	 * @param description
	 * @return
	 */
	public void saveJobOrderExe(String orderCode, String processId, String processName, String orgId, String userId,
								String facCode, String status, String reworkReason, String description) {
		String facId = null;
		if (facCode != null && !"".equals(facCode)) {
			Map<String, Object> map = getFacilitiesByCode(facCode);
			if (map != null) {
				facId = (String) map.get("Eam_Facilities_ID");
				//验证设备是否在工序的关联类型中
				if (!checkFacilitiesInProcess(processId, facId)) {
					throw new RuntimeException("设备[" + facCode + "]不存在于" + processName + "关联的设备类型中，请检查是否扫描错误！");
				}
			} else {
				throw new RuntimeException("设备[" + facCode + "]不存在！");
			}
		}
	}

	/**
	 * 更新工单工序信息
	 *
	 * @param orderCode
	 * @param userId
	 * @param facCode
	 */
	public String updateJobOrder(String orderCode, String processId, String processName, String userId, String facCode, String status) {
		String facId = null;
		if (facCode != null && !"".equals(facCode)) {
			Map<String, Object> map = getFacilitiesByCode(facCode);
			if (map != null) {
				facId = (String) map.get("Eam_Facilities_ID");
				//验证设备是否在工序的关联类型中
				if (!checkFacilitiesInProcess(processId, facId)) {
					throw new RuntimeException("设备[" + facCode + "]不存在于" + processName + "关联的设备类型中，请检查是否扫描错误！");
				}
			} else {
				throw new RuntimeException("设备[" + facCode + "]不存在！");
			}
		}

		//修改创建状态工序为开工状态
		String sql = "update Emeso_Job_Order_Exe oe " +
				" set status = 'START', ad_user_Id = ?, exe_date_from = now() " +
				" where exists(select 1 from Emeso_Job_Order jo " +
				"				where jo.Emeso_Job_Order_id = oe.Emeso_Job_Order_id " +
				"				  and jo.code = ?) " +
				"	and status = 'CREATE' " +
				"	and emes_process_id = ? ";

		if ("START".equalsIgnoreCase(status)) {
			//修改开工状态工序为完工状态
			sql = "update Emeso_Job_Order_Exe oe " +
					" set status = 'FINISHED', ad_user_Id = ?, exe_date_to = now() " +
					" where exists(select 1 from Emeso_Job_Order jo " +
					"				where jo.Emeso_Job_Order_id = oe.Emeso_Job_Order_id " +
					"				  and jo.code = ?) " +
					"	and status = 'START' " +
					"	and emes_process_id = ? ";
		}
		if (dao.executeSql(sql, userId, orderCode, processId) > 0) {
			return status;
		}
		return null;
	}

	/**
	 * 根据编码获取设备
	 */
	public Map<String, Object> getFacilitiesByCode(String facCode) {
		String sql = "select Eam_Facilities_Id, nvl(run_Status, 'IDLE') status , emeso_manu_batch_id " +
				"       ,(select mb.code from emeso_manu_batch mb " +
				"          where mb.emeso_manu_batch_id=f.emeso_manu_batch_id) manu_batch_code " +
				"from Eam_Facilities f where upper(code) = ?";
		Map<String, Object> map = dao.queryUnique(sql, facCode.toUpperCase());
		return map;
	}

	/**
	 * 验证设备是否与工序关联
	 * @param processId
	 * @param facId
	 * @return
	 */
	private boolean checkFacilitiesInProcess(String processId, String facId) {
		String sql = "select count(*) count0 from Eam_Facilities f where (exists (" +
				"    select 1 from Emes_Process_Facilities pf" +
				"     where pf.Eam_Facilities_id = f.Eam_Facilities_id" +
				"       and pf.Eam_Facilities_id is not null" +
				"       and pf.emes_process_id = ?" +
				"  ) or exists (" +
				"    select 1 from Emes_Process_Facilities pf" +
				"     where pf.facilities_type = f.fancility_type" +
				"       and pf.Eam_Facilities_id is null" +
				"       and pf.emes_process_id = ?" +
				"  )) and f.Eam_Facilities_id = ?";
		return dao.getSqlCount(sql, processId, processId, facId) > 0;
	}

	/**
	 * 添加工序上料记录
	 *
	 * @param batchProcessId
	 * @param batchNo
	 * @param productId
	 * @param qty
	 * @param uomId
	 * @param type
	 * @param description
	 * @param orgId
	 * @param userId
	 */
	public void saveBatchInMaterial(String batchProcessId, String batchNo, String productId, double qty,
									String uomId, String type, String description, String orgId, String userId) {
		String sql = "insert into Emeso_Manu_In_Material (Emeso_Manu_In_Material_id, Emeso_Job_Order_Exe_Id, " +
				"Batch_No, M_Product_Id, Qty, Qty_Get, C_Uom_Id, Type, Description, ad_org_id, createdby, updatedby) " +
				"values (sys_guid(), ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

		dao.executeSql(sql, batchProcessId, batchNo, productId, qty, qty, uomId, type,
				description, orgId, userId, userId);
	}

	/**
	 * 验证工单的上道工序是否已完工
	 *
	 * @param orderCode
	 * @param processId
	 * @return
	 */
	public String checkBatchProcess(String orderCode, String processId) {
		String sql = "select check_last_process_status(?, ?) msg from dual";
		Map<String, Object> map = dao.queryUnique(sql, orderCode, processId);
		if (map != null) {
			return (String) map.get("MSG");
		}
		return null;
	}

	/**
	 * 验证工单当前工序是否为返工
	 *
	 * @param orderCode
	 * @param processId
	 * @return
	 */
	public String checkBatchProcessRework(String orderCode, String processId) {
		String sql = "select Mes_Check_Batch_Process_Rework(?, ?) msg from dual";
		Map<String, Object> map = dao.queryUnique(sql, orderCode, processId);
		if (map != null) {
			return (String) map.get("MSG");
		}
		return null;
	}

	/**
	 * 获取工序列表
	 * @param orgId
	 * @return
	 */
	public List<Map<String, Object>> getEmesProcessList(String orgId) {
		String sql = "select p.emes_process_id, p.name " +
				" from emes_process p, emes_process_route pr " +
				" where pr.emes_process_route_id=p.emes_process_route_id " +
				"       and pr.ad_org_id =? " +
				" order by route_no";
		List<Map<String, Object>> list = this.dao.query(sql, orgId);
		return list;
	}

	public String geteUserId(String userCode) {
		String sql = "select ad_user_id from ad_user where username = ?";
		Map user = dao.queryUnique(sql, userCode);
		if (user != null) {
			return (String) user.get("ad_user_id");
		}
		return null;
	}

	/**
	 * 根据作业工单号，和工艺id获取作业指导书
	 */
	public String getSopFileId(String orderCode, String processOprId) {
		String sql = "select p.workroute from emes_process_route pr, emes_process p, emeso_job_order jo\n" +
				" where pr.emes_process_route_id = p.emes_process_route_id\n" +
				"   and pr.m_product_id = jo.m_product_id\n" +
				"	and nvl(p.workroute, '') <> ''" +
				"\t and p.emes_processopr_id = ?\n" +
				"\t and jo.code = ?";
		Map<String, Object> result = dao.queryUnique(sql, processOprId, orderCode);
		if (result != null) {
			return (String)result.get("workroute");
		}
		return null;
	}
}